using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;

using KNCApp.Business;
using KNCApp.Business.Models;
using KNCApp.Business.Interfaces;

using FirebirdSql.Data.FirebirdClient;


namespace KNCApp.DataSource.DB
{
    class DBManager : IDataSource
    {
        private static IDataSource instance;

        private FbConnection defConnection;
        private FbConnection connection;

        private DBManager()
        {
        }
        
        public static IDataSource GetInstance()
        {
            if (instance == null)
            {
                instance = new DBManager();
            }
            return instance;
        }
        
        public bool InitConnection()
        {
            bool ret = true;
            try
            {
                if (connection == null)
                {
                    FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
                    csb.ServerType = FbServerType.Embedded;
                    csb.Database = SettingsMgr.GetDBPath();

                    connection = new FbConnection(csb.ToString());
                }
                connection.Open();
                defConnection = connection;
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while trying to establish DB connection\n" + ex.ToString());
                return false;
            }
            return ret;
        }

        public void CloseConnection()
        {
            try
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while trying to close DB connection\n" + ex.ToString());
            }
        }

        public bool CreateArchConnection(string dbPath)
        {
            bool ret = true;
            if (dbPath.ToUpper() == connection.Database.ToUpper())
            {
                return true;
            }
            try
            {
                FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
                csb.ServerType = FbServerType.Embedded;
                csb.Database = dbPath;
                connection = new FbConnection(csb.ToString());
                connection.Open();
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while trying to establish connection to archive DB\n" + ex.ToString());
                return false;
            }
            return ret;
        }

        public void RestoreDefConnection()
        {
            if (connection.Database.ToUpper() != defConnection.Database.ToUpper())
            {
                try
                {
                    if (connection != null && connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection = defConnection;
                }
                catch (Exception ex)
                {
                }
            }
        }

        public int GetNextRecid(string genName)
        {
            return GetNextRecid(genName, null);
        }

        public int GetNextRecid(string genName, FbTransaction tran)
        {
            int recId = -1;
            string commText = "SELECT gen_id(" + genName + ", 1) from rdb$database";
            FbCommand cmd = new FbCommand(commText, connection);
            if (tran != null)
            {
                cmd.Transaction = tran;
            }
            try
            {
                recId = System.Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while generating next RECID for " + genName + "\n" + ex.ToString());
            }
            cmd.Dispose();

            return recId;
        }


        public bool AddPilot(Pilot pilot)
        {
            bool ret = true;
            FbCommand cmd = new FbCommand(SQLCommands.INSERT_PILOT, connection);
            try
            {
                if (pilot.ID < 1)
                {
                    pilot.ID = GetNextRecid("ID_PILOTA_GEN");
                }
                cmd.Parameters.Add("@ID_PILOTA", pilot.ID);
                cmd.Parameters.Add("@NAZWISKO", pilot.LastName);
                cmd.Parameters.Add("@IMIE", pilot.FirstName);
                cmd.Parameters.Add("@DATA_URODZENIA", pilot.DoB);
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ret = false;
                Program.LogFatalError("Error while adding new pilot\n" + ex.ToString());
            }
	        cmd.Dispose();

            return ret;
        }

        public bool AddTests(List<TestPilot> tests)
        {
            bool ret = true;
            if(tests.Count == 0)
                return ret;
            int tstID = tests[0].Test.ID;
            FbTransaction tran = null;
            FbCommand cmd1 = null;
            FbCommand cmd2 = null;
            try
            {
                if (tstID < 1)
                {
                    tstID = GetNextRecid("ID_BADANIA_GEN");
                }
                tran = connection.BeginTransaction();
                cmd1 = new FbCommand(SQLCommands.INSERT_TEST, connection);
                cmd1.Transaction = tran;
                cmd1.Parameters.Add("@ID_BADANIA", tstID);
                cmd1.Parameters.Add("@DATA_BADANIA", tests[0].Test.Date);
                cmd1.Parameters.Add("@OPIS", tests[0].Test.Name);
                cmd1.ExecuteNonQuery();

                cmd2 = new FbCommand(SQLCommands.INSERT_PILOT_TEST, connection);
                cmd2.Transaction = tran;
                cmd2.Parameters.Add("@ID_BAD_PILOTA", FbDbType.Integer);
                cmd2.Parameters.Add("@ID_PILOTA", FbDbType.Integer);
                cmd2.Parameters.Add("@ID_BADANIA", FbDbType.Integer);
                cmd2.Parameters.Add("@STANOWISKO", FbDbType.VarChar);
                cmd2.Prepare();
                foreach (TestPilot tp in tests)
                {
                    tp.Test.ID = tstID;
                    tp.ID = GetNextRecid("ID_BADANIA_PILOTA_GEN", tran);
                    cmd2.Parameters["@ID_BAD_PILOTA"].Value = tp.ID;
                    cmd2.Parameters["@ID_PILOTA"].Value = tp.Pilot.ID;
                    cmd2.Parameters["@ID_BADANIA"].Value = tp.Test.ID;
                    cmd2.Parameters["@STANOWISKO"].Value = tp.Position;
                    cmd2.ExecuteNonQuery();
                }
                
                tran.Commit();

            }
            catch (Exception ex)
            {
                tran.Rollback();
                ret = false;
                Program.LogFatalError("Error while adding new test\n" + ex.ToString());
            }
            cmd1.Dispose();
            cmd2.Dispose();

            return ret;
        }

        public bool AddResult(Result result)
        {
            bool ret = true;
            FbCommand cmd = new FbCommand(SQLCommands.INSERT_RESULT, connection);
            try
            {
                cmd.Parameters.Add("@ID_WYNIKU", result.ID);
                cmd.Parameters.Add("@ID_BAD_PILOTA", result.TestPilot.ID);
                cmd.Parameters.Add("@ETCO2", result.ETCO2);
                cmd.Parameters.Add("@ODDECH", result.Breath);
                cmd.Parameters.Add("@SPO2", result.SPO2);
                cmd.Parameters.Add("@TETNO", result.Pulse);
                if (result.Pressure != null )
                {
                    cmd.Parameters.Add("@CISNIENIE", result.Pressure.Value);
                }
                else
                {
                    cmd.Parameters.Add("@CISNIENIE", DBNull.Value);  
                }
                cmd.Parameters.Add("@CZAS", result.Time);
                cmd.Parameters.Add("@STAN", result.State);
                cmd.Parameters.Add("@TLEN", result.Tlen == true ? "Y" : "N");
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ret = false;
                Program.LogFatalError("Error while adding new result\n" + ex.ToString());
            }
            cmd.Dispose();

            return ret;
        }
        
        private void appendCondition(StringBuilder sb, string cond, bool forceAND)
        {
            if (forceAND || sb.Length > 0)
            {
                sb.Append(" AND ");
            }
            sb.Append(cond);
        }

        private string getString(FbDataReader fbr, int argInd)
        {
            if (!fbr.IsDBNull(argInd))
                return fbr.GetString(argInd);
            return null;
        }

        public static int getInt(FbDataReader fbr, int argInd)
        {
            if (!fbr.IsDBNull(argInd))
                return fbr.GetInt32(argInd);
            return Int32.MinValue;
        }

        public static decimal getDecimal(FbDataReader fbr, int argInd)
        {
            if (!fbr.IsDBNull(argInd))
                return fbr.GetDecimal(argInd);
            return decimal.MinValue;
        }


        private DateTime getDate(FbDataReader fbr, int argInd)
        {
            if (!fbr.IsDBNull(argInd))
                return fbr.GetDateTime(argInd);
            return DateTime.MinValue;
        }

        public List<Pilot> SelectPilots(SearchCriteria sc)
        {
            List<Pilot> pilots = new List<Pilot>();
            
            StringBuilder pilotConds = new StringBuilder();
            if(sc.LastName != null)
            {
                appendCondition(pilotConds, "UPPER(NAZWISKO) like '" + sc.LastName.ToUpper() + "%'", false);
            }
            if (sc.FirstName != null)
            {
                appendCondition(pilotConds, "UPPER(IMIE) like '" + sc.FirstName.ToUpper() + "%'", false);
            }
            if (sc.DobFrom != DateTime.MinValue)
            {
                appendCondition(pilotConds, "DATA_URODZENIA >= '" + sc.DobFrom.ToShortDateString() + "'", false);
            }
            if (sc.DobTo != DateTime.MinValue)
            {
                appendCondition(pilotConds, "DATA_URODZENIA <= '" + sc.DobTo.ToShortDateString() + "'", false);
            }
            if (sc.TestName != null || sc.TestFrom != DateTime.MinValue || sc.TestTo != DateTime.MinValue)
            {
                StringBuilder testConds = new StringBuilder();
                if (sc.TestName != null)
                {
                    appendCondition(testConds, "UPPER(OPIS) like '%" + sc.TestName.ToUpper() + "%'", true);
                }
                if (sc.TestFrom != DateTime.MinValue)
                {
                    appendCondition(testConds, "DATA_BADANIA >= '" + sc.TestFrom.ToShortDateString() + "'", true);
                }
                if (sc.TestTo != DateTime.MinValue)
                {
                    appendCondition(testConds, "DATA_BADANIA <= '" + sc.TestTo.ToShortDateString() + "'", true);
                }
                string condStr = "EXISTS(SELECT ID_BAD_PILOTA FROM BADANIA_PILOTA, BADANIA WHERE "+
                                 "BADANIA_PILOTA.ID_PILOTA = PILOCI.ID_PILOTA AND BADANIA_PILOTA.ID_BADANIA = BADANIA.ID_BADANIA " +
                                 testConds.ToString() + ")";
                appendCondition(pilotConds, condStr, false); 
                
            }
            if (pilotConds.Length > 0)
            {
                pilotConds.Insert(0, "WHERE ");
            }
            pilotConds.Insert(0, SQLCommands.SELECT_PILOTS);
            FbCommand cmd = new FbCommand(pilotConds.ToString(), connection);

            FbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                Pilot pilot = null;
                while (reader.Read())
                {
                    pilot = new Pilot();
                    pilot.ID = getInt(reader, 0);
                    pilot.LastName = getString(reader, 1);
                    pilot.FirstName = getString(reader, 2);
                    pilot.DoB = getDate(reader, 3);
                    pilots.Add(pilot);
                }
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while selecting pilots\n" + ex.ToString());
            }

            reader.Close();
            cmd.Dispose();

            return pilots;
        }

        public List<TestPilot> SelectTests(SearchCriteria sc)
        {
            List<TestPilot> tests = new List<TestPilot>();

            StringBuilder testConds = new StringBuilder();
            if (sc.LastName != null)
            {
                appendCondition(testConds, "UPPER(NAZWISKO) like '" + sc.LastName.ToUpper() + "%'", true);
            }
            if (sc.FirstName != null)
            {
                appendCondition(testConds, "UPPER(IMIE) like '" + sc.FirstName.ToUpper() + "%'", true);
            }
            if (sc.DobFrom != DateTime.MinValue)
            {
                appendCondition(testConds, "DATA_URODZENIA >= '" + sc.DobFrom.ToShortDateString() + "'", true);
            }
            if (sc.DobTo != DateTime.MinValue)
            {
                appendCondition(testConds, "DATA_URODZENIA <= '" + sc.DobTo.ToShortDateString() + "'", true);
            }
            if (sc.TestName != null)
            {
                appendCondition(testConds, "UPPER(OPIS) like '%" + sc.TestName.ToUpper() + "%'", true);
            }
            if (sc.TestFrom != DateTime.MinValue)
            {
                appendCondition(testConds, "DATA_BADANIA >= '" + sc.TestFrom.ToShortDateString() + "'", true);
            }
            if (sc.TestTo != DateTime.MinValue)
            {
                appendCondition(testConds, "DATA_BADANIA <= '" + sc.TestTo.ToShortDateString() + "'", true);
            }
            testConds.Append(" ORDER BY DATA_BADANIA"); 
            testConds.Insert(0, SQLCommands.SELECT_TESTS);
            FbCommand cmd = new FbCommand(testConds.ToString(), connection);

            FbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                Test test = null;
                Pilot pilot = null;
                TestPilot testPilot = null;
                while (reader.Read())
                {
                    testPilot = new TestPilot();
                    testPilot.ID = getInt(reader, 0);
                    test = new Test();
                    test.ID = getInt(reader, 1);
                    test.Date = getDate(reader, 2);
                    test.Name = getString(reader, 3);
                    testPilot.Test = test;
                    pilot = new Pilot();
                    pilot.ID = getInt(reader, 4);
                    pilot.LastName = getString(reader, 5);
                    pilot.FirstName = getString(reader, 6);
                    pilot.DoB = getDate(reader, 7);
                    testPilot.Pilot = pilot;
                    testPilot.Position = getString(reader, 8);
                    tests.Add(testPilot);
                }
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while selecting tests\n" + ex.ToString());
            }

            reader.Close();
            cmd.Dispose();

            return tests;
        }

        public List<Result> SelectResults(TestPilot tp)
        {
            List<Result> results = new List<Result>();
            FbCommand cmd = new FbCommand(SQLCommands.SELECT_RESULTS, connection);
            cmd.Parameters.Add("@ID_BAD_PILOTA", tp.ID);
            FbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                Result res = null;
                while (reader.Read())
                {
                    res = new Result();
                    res.ID = getInt(reader, 0);
                    res.TestPilot = tp;
                    res.ETCO2 = getDecimal(reader, 1);
                    res.Breath = getInt(reader, 2);
                    res.SPO2 = getInt(reader, 3);
                    res.Pulse = getInt(reader, 4);
                    res.Pressure = new Pressure(-1, getDecimal(reader, 5), "");
                    res.Time = getDate(reader, 6);
                    res.State = (TestState)getInt(reader, 7);
                    res.Tlen = getString(reader, 8) == "Y" ? true : false;
                    results.Add(res);
                }
            }
            catch (Exception ex)
            {
                Program.LogFatalError("Error while selecting results\n" + ex.ToString());
            }

            reader.Close();
            cmd.Dispose();

            return results;
        }

    }
}
